package com.btks.jswater.mapper;

import com.btks.jswater.entity.*;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author ZhangKewen
 * @since 2021-12-22
 */
public interface RiverInfoMapper extends BaseMapper<RiverInfo> {
    @Select("SELECT * FROM \"water_relation\" where id = '${id}'")
    List<WaterRelation> getRela(@Param("id") String id);

    @Select("select * from water_direction where id= '${id}'")
    List<WaterDirections> getDirections(@Param("id") String id);

    @Select("SELECT stcd as stcd, stname as name ,lx,x,y FROM \"water_relations_point\" where name='${id}'")
    List<WaterRelationPoint> getShuizha(@Param("id") String id);

    @Select("SELECT * FROM \"water_relations_point\" where lx='分水计量点'")
    List<WaterRelationPoint> getPointCount();

    @Select("SELECT * FROM \"water_relations_point\" where lx='分水计量点' and code=${id}")
    List<WaterPointRelationTable> getCode(@Param("id") int id);

    @Select("SELECT * FROM \"water_moni_data\" WHERE \"ST_CODE\"='${id}'")
    List<WaterMoniData> getSiteInfo(@Param("id") String id);

    @Select("SELECT * FROM \"water_moni_data\" where  to_char(\"TM\",'YYYY-MM-DD hh24:mi:ss')>='${stime} 00:00:00' and  to_char(\"TM\",'YYYY-MM-DD hh24:mi:ss')<='${etime} 23:59:59' and \"ST_CODE\"='${scode}'")
    List<WaterMoniData> getInfoByTime(@Param("stime") String stime,@Param("etime") String etime,@Param("scode") String scode);

    /***
     *获取渠系关系表中各个站点的当天平均流量
     * **/
    @Select("SELECT COALESCE(ROUND(AVG(\"ST_W\"),3),0)FROM \"water_moni_data\" where  to_char(\"TM\",'YYYY-MM-DD hh24:mi:ss')>='${stime} 00:00:00' and  to_char(\"TM\",'YYYY-MM-DD hh24:mi:ss')<='${etime} 23:59:59' and \"ST_CODE\"='${scode}'")
    float getTableCountInfo(@Param("stime") String stime,@Param("etime") String etime,@Param("scode") String scode);
    /***
     *获取渠系关系表中最新一条数据的时间
     * **/
    @Select("SELECT \"TM\" FROM \"water_moni_data\" where \"ST_CODE\"='${stcode}' order by \"TM\" desc limit 1")
    String updateTime(@Param("stcode") String stcode);




    /**
     * 查询每个水库各个监测站日期内的水量情况
     *  * **/
    @Select("SELECT b.\"ST_CODE\",COALESCE(A.total,0)as total,B.\"ST_NAME\"FROM(SELECT\t\"ST_CODE\" AS code,\tSUM ( \"ST_W\" ) AS total FROM\twater_moni_data WHERE\t\"ST_CODE\" IN ( SELECT stcd FROM \"water_relations_point\" WHERE NAME = '${code}' ) AND \"TM\" > '${stime}' AND \"TM\" < '${etime}' GROUP BY\t\"ST_CODE\" ) AS A right JOIN (SELECT DISTINCT(\"ST_CODE\"),\"ST_NAME\" from water_moni_data)as b on a.code=b.\"ST_CODE\"")
    List<Map> getMoniData(@Param("code") String st,@Param("stime") String stime,@Param("etime") String etime);

    /**
     * 查询每个水库龙口的水量情况
     */
    @Select("select * from water_moni_data where \"ST_CODE\" in ( SELECT stcd FROM \"water_shuiku\" where id='${code}') and \"TM\">'${stime}' and \"TM\"<'${etime}' ")
    List<WaterMoniData> getShuikuData(@Param("code") String code,@Param("stime") String stime,@Param("etime") String etime);
}


